Dynamic Queries

This topic describes the dynamic queries mechanism supported by the WS-AppServer connector.

The default implementation of Process Platform requires users to specify a static query during creation of a Web service operation, thereby creating new Web service operations for similar queries. A generic query to a database results in a large dataset and the client needs to process this huge dataset to find the required information.

Dynamic query is a mechanism that enables users to enhance a query in order to generate appropriate requests at run time. The dynamic filter and sort features provided by dynamic query enable a query to consume parameters at run time, thereby reducing the dataset size available to the client. This drastically reduces the amount of processing needed at the client side and allows the client to have a single Web service operation with the flexibility of using additional parameters at run time.

Dynamic Filter

In dynamic filter queries, a part of the filter is passed as a parameter. This allows flexibility, and therefore, more control over the filtering mechanism.
For example, consider the following query:

SELECT * FROM Orders WHERE OrderID = :OrderID AND :DF

Here, the parameter :DF represents a dynamic filter. The value of :DF may be any condition, such as 'Price > 100'. In that case, the final query will be as follows:

SELECT * FROM Orders WHERE OrderID = :OrderID AND (Price > 100)

The dynamic filter is specified at the time of query execution. The syntax for representing the dynamic filter condition is as follows:

<opr attr="column" dd="dd value" dt="dt value">
    <value>somevalue</value> OR <attribute>someattribute</attribute>
</opr>

Here,

opr is the operator tag and represents the operation that needs to be performed.
attr is the column of the table on which the condition is applied. This value will be used as the parameter place holder name.
dd represents the metadata of the tags. It specifies the data type definition with respect to the DB type. This tag is mandatory. This will be used dynamically in the condition part of the actual query formed.
dt also represents the metadata of the tags. It specifies the data type definition with respect to the DB type explicitly. This tag is mandatory. It denotes the data type of the parameter.
value is the actual value that is used for the operation.
attribute is the name of another column or attribute of the table. This can be specified instead of value in case the operation is to be performed with respect to another column instead of a fixed value.

For example, the condition 'Price > 100' is represented as follows:

<gt attr="Price" dd="Orders.Price" dt="i4">
    <value>100</value>
</gt>

The filter condition 'InsideColor = OutsideColor' is represented as follows:

<eq attr="InsideColor" dd="Color.InsideColor" dt="string">
    <attribute>OutsideColor</attribute>
</eq>

The dynamic filter is passed as a parameter to the XQY. Consider a Web service operation GetOrdersWithFilter with the following implementation:

<implementation type="DBSQL">
    <constructor language="DBSQL">
        <query>SELECT * FROM Orders WHERE OrderID BETWEEN :FromOrderID AND :ToOrderID AND :DF</query>
        <parameters>
            <FromOrderID dd="Orders.OrderID" dt="i4"/>
            <ToOrderID dd="Orders.OrderID" dt="i4"/>
            <DF type="dfilter"/>
        </parameters>
    </constructor>
</implementation>

The sample SOAP request for GetOrdersWithFilter is as follows:

<SOAP:Body>
    <GetOrdersWithFilter xmlns="http://schemas.cordys.com/1.0/dfilter">
        <FromOrderID>10248</FromOrderID>
        <ToOrderID>10281</ToOrderID>
        <DF type="dfilter">
            <eq attr="EmployeeID" dd="Orders.EmployeeID" dt="i4">
                <value>5</value>
            </eq>
        </DF>
    </GetOrdersWithFilter>
</SOAP:Body>

The response is as follows:

<data>
    <GetOrdersWithFilterResponse xmlns="http://schemas.cordys.com/1.0/dfilter">
        <tuple>
            <old>
                <Orders>
                    <OrderID>10248</OrderID>
                    <CustomerID>VINET</CustomerID>
                    <EmployeeID>5</EmployeeID>
                    <OrderDate>1996-07-16T00:00:00.0</OrderDate>
                    <RequiredDate>2001-08-19T00:00:00.0</RequiredDate>
                    <ShippedDate>1996-07-16T00:00:00.0</ShippedDate>
                    <ShipVia>3</ShipVia>
                    <Freight>32.38</Freight>
                    <ShipName>Vins</ShipName>
                    <ShipAddress>59 rue de l'Abbayebgv</ShipAddress>
                    <ShipCity>Reims</ShipCity>
                    <ShipRegion null="true"/>
                    <ShipPostalCode>51100</ShipPostalCode>
                    <ShipCountry>France</ShipCountry>
                </Orders>
            </old>
        </tuple>
        <tuple>
            <old>
                <Orders>
                    <OrderID>10254</OrderID>
                    <CustomerID>CHOPS</CustomerID>
                    <EmployeeID>5</EmployeeID>
                    <OrderDate>1996-07-11T00:00:00.0</OrderDate>
                    <RequiredDate>1996-08-08T00:00:00.0</RequiredDate>
                    <ShippedDate>1996-07-23T00:00:00.0</ShippedDate>
                    <ShipVia>2</ShipVia>
                    <Freight>22.98</Freight>
                    <ShipName>Chop-suey Chinese</ShipName>
                    <ShipAddress>Hauptstr. 31</ShipAddress>
                    <ShipCity>Bern</ShipCity>
                    <ShipRegion null="true"/>
                    <ShipPostalCode>3012</ShipPostalCode>
                    <ShipCountry>Switzerland</ShipCountry>
                </Orders>
            </old>
        </tuple>
        <tuple>
            <old>
                <Orders>
                    <OrderID>10269</OrderID>
                    <CustomerID>WHITC</CustomerID>
                    <EmployeeID>5</EmployeeID>
                    <OrderDate>1996-07-31T00:00:00.0</OrderDate>
                    <RequiredDate>1996-08-14T00:00:00.0</RequiredDate>
                    <ShippedDate>1996-08-09T00:00:00.0</ShippedDate>
                    <ShipVia>1</ShipVia>
                    <Freight>4.56</Freight>
                    <ShipName>White Clover Markets</ShipName>
                    <ShipAddress>1029 - 12th Ave. S.</ShipAddress>
                    <ShipCity>Seattle</ShipCity>
                    <ShipRegion>WA</ShipRegion>
                    <ShipPostalCode>98124</ShipPostalCode>
                    <ShipCountry>USA</ShipCountry>
                </Orders>
            </old>
        </tuple>
    </GetOrdersWithFilterResponse>
</data>

Note: Refer to Adding Dynamic Filters for information about adding filters to the implementation and interface of the Web service operation.

Dynamic Sort

Dynamic sort is another mechanism by which search operations can be made faster. Similar to dynamic filter, the dynamic sort is also passed as a parameter in the query. For example, consider the following query:

SELECT * FROM Orders WHERE w ORDER BY :DS

Here, :DS represents the dynamic sort parameter. The value of :DS can be any column that must be used for the sort operation, such as 'Status'. In that case, the final query is:

SELECT * FROM Orders WHERE w ORDER BY Status

The dynamic sort is specified at the time of query execution. The syntax for representing the dynamic sort condition is as follows:

<DS> <column [order='sortorder']/> </DS>

Here,

column refers to the column used for sorting.
order can be either 'asc' (ascending) or 'desc' (descending). By default, the sort order will be ascending.

If the results have to be sorted by the Status attribute, then the corresponding parameter value will be defined in the query as follows:

<DS> <Status [order='asc']/> </DS>

Consider a Web service operation GetOrdersBySort with the following implementation:

<implementation type="DBSQL">
    <constructor language="DBSQL">
        <query>SELECT * FROM Orders WHERE OrderID BETWEEN :FromOrderID AND :ToOrderID order by :DS</query>
        <parameters>
            <FromOrderID dd="Orders.OrderID" dt="i4"/>
            <ToOrderID dd="Orders.OrderID" dt="i4"/>
            <DS type="dsort"/>
        </parameters>
    </constructor>
</implementation>

The sample SOAP request for GetOrdersBySort is as follows:

<SOAP:Body>
    <GetOrdersBySort xmlns="http://schemas.cordys.com/1.0/dsort"> <FromOrderID>10248</FromOrderID>
        <ToOrderID>10252</ToOrderID>
        <DS type="dsort">
            <CustomerID order="desc"/>
    </GetOrdersBySort >
        </DS>
</SOAP:Body>

The response is as follows:

<SOAP:Body>
    <GetOrdersBySort xmlns="http://schemas.cordys.com/1.0/dsort">
        <FromOrderID>10248</FromOrderID>
        <ToOrderID>10252</ToOrderID>
        <DS type="dsort">
            <CustomerID order="desc"/>
        </DS>
    </GetOrdersBySort>
</SOAP:Body>

The SQL Server OFFSET-FETCH clause (applicable only for SQL Server 2012 or higher versions) can be used with dynamic sort. The below examples demonstrate the use of the OFFSET-FETCH clause.

The sample Web service implementation to retrieve the first 10 rows from the sorted result set is as follows:

<implementation type="DBSQL">
    <constructor language="DBSQL">
        <query>SELECT EmployeeID,LastName,FirstName FROM Employees ORDER BY :DS</query>
        <parameters>
            <DS type="dsort">
                <EmployeeID order="ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY"/>
            </DS>
        </parameters>
    </constructor>
</implementation>

Skip the first 10 rows from the sorted result set and return the next 5 rows.

<implementation type="DBSQL">
    <constructor language="DBSQL">
        <query>SELECT EmployeeID,LastName,FirstName FROM Employees ORDER BY :DS</query>
        <parameters>
            <DS type="dsort">
                <EmployeeID order="ASC OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY"/>
            </DS>
        </parameters>
    </constructor>
</implementation>

Note: With dynamic filter, one can only reduce the result set, but not widen it.

The default dynamic filter substitution is 1=1. This implies that if a request has a dynamic part in the query and no value set for the parameters, then the query must be processed with an assumed substitute value of 1=1.

For example, if a Web service operation has an implementation that contains the
following query tag
<query>
SELECT * FROM Orders WHERE OrderID BETWEEN
:FromOrderID AND :ToOrderID AND :DF
</query>
and the user request has the following parameters:
<FromOrderID>1</FromOrderID>
<ToOrderID>5</ToOrderID>
<DF type='dfilter' />
then, the final query will result as follows:
SELECT * FROM Orders WHERE OrderID BETWEEN :FromOrderID AND
:ToOrderID AND 1=1

Note: The default value of sort is asc. If no order attribute is specified in the dynamic sort parameter, asc must be considered.

The dynamic filter parameters are substituted with an arbitrary value 'Px', where 'x' is the number of the parameter in the request. The dynamic filter substituted in the original query is separated by parenthesis. Also, parameters containing AND and OR operators are also separated by inner parenthesis. For example:

<DF type="dfilter">
    <and>
        <or>
<gt attr="OrderID" dd=Orders.OrderID" dt="i4"><value>13333</value></gt>
<eq attr="OrderID" dd=Orders.OrderID" dt="i4><value>12440</value></eq>
        </or>
<lt attribute="Price" dd="Orders.Price" dt="i8"><value>1000.00</value></lt>
    </and>
</DF>

This can be replaced with the following:

((OrderID>:P1 OR OrderID=:P2) AND Price<:P3))

The query containing a dynamic filter must contain a logical operator 'AND' preceding the dynamic filter part. The absence of this will result in an error.

The user can opt not to validate the dynamic query (for the 'AND' clause) before preparation, by adding an attribute dverify=no in the query. By default, dverify will be 'yes' (it will validate for the 'AND' clause). The dverify attribute must be in the constructor tag of the Web service operation implementation. For example:

<implementation type="DBSQL">
    <constructor dverify="no" language="DBSQL">
        <query>select * from Employees where :DF1 OR :DF2</query>
        <parameters>
            <DF1 type="dfilter"/>
            <DF2 type="dfilter"/>
        </parameters>
    </constructor>
</implementation>

Note: The user cannot provide more than one dynamic sort in a single Order by clause.

List of Supported Operations

Operation	Tag Name	Number of Operands
Logical AND	<and>	None
Logical ON	<or>	None
Logical NOT	<not>	1

Below is the list of comparison operations supported in dynamic queries:

Operation	Tag Name	Number of Operands
Equals	<eq>	2
Not Equal to	<ne>	2
Greater than	<gt>	2
Greater than or equal to	<>	2
Less than	<lt>	2
Less than or equal to	<le>	2
Like	<like>	2
Is NULL	<isnull>	1
Is not NULL	<isnotnull>	1
Between	<between >	3
In	<in>	None

Consider the following implementation for 'complexmethod':

<implementation type="DBSQL">
    <constructor language="DBSQL">
        <query>select Products.ProductID, Products.ProductName, Products.SupplierID, Products.CategoryID, Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued, Suppliers.CompanyName,Suppliers.ContactName, Suppliers.SupplierID,Suppliers.City, Categories.CategoryID,Categories.CategoryName,Categories.Description from Products,Suppliers,Categories where Products.SupplierID = Suppliers.SupplierID and :DF1 and Products.CategoryID = Categories.CategoryID and :DF2 order by :DS </query>
        <parameters>
            <DF1 type="dfilter"/>
            <DF2 type="dfilter"/>
            <DS type="dsort"/>
        </parameters>
    </constructor>
</implementation>

The sample SOAP request is as follows:

<SOAP:Body>
<complexFilters xmlns="http://schemas.cordys.com/1.0/dfilter">
        <DF1 type="dfilter">
            <and>
                <like attr="ProductName" dd="Products.ProductName" dt="string">
                    <value>%h%</value>
                </like>
                <in attr="CompanyName" dd="Suppliers.CompanyName" dt="string">
                    <value>Exotic Liquids</value>
                    <value>Tokyo Traders</value>
                    <value>New Orleans Cajun Delights</value>
                    <value>Leka Trading</value>
                </in>
                <lt attr="ProductID" dd="Products.ProductID" dt="i4">
                    <value>100</value>
                </lt>
            </and>
        </DF1>
        <DF2 type="dfilter">
            <and>
                <like attr="CategoryName" dd="Categories.CategoryName" dt="string">
                    <value>%e%</value>
                </like>
                <eq attr="Categories.CategoryID" dd="Categories.CategoryID" dt="i4">
                    <value>1</value>
                </eq>
            </and>
        </DF2>
        <DS type="dsort">
            <ProductName/>
        </DS>
    </complexFilters>
<SOAP:Body>

An equivalent Web service operation implementation for 'complexmethod' is as follows:

<implementation type="DBSQL">
    <constructor language="DBSQL">
        <query>select Products.ProductID, Products.ProductName, Products.SupplierID, Products.CategoryID,
        Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock, Products.UnitsOnOrder,
        Products.ReorderLevel, Products.Discontinued, Suppliers.CompanyName,Suppliers.ContactName,
        Suppliers.SupplierID,Suppliers.City, Categories.CategoryID,Categories.CategoryName,Categories.Description
        from Products,Suppliers,Categories where Products.SupplierID = Suppliers.SupplierID and ( ProductName like
        ( :P1 ) and CompanyName IN (:P2) and ProductID &amp;lt; :P3 ) and Products.CategoryID = Categories.CategoryID
        and ( CategoryName like ( :P4 ) and Categories.CategoryID = :P5 ) order by ProductName
        </query>
        <parameters>
            <P1 dd="Products.ProductName" dt="string"/>
            <P2 dd="Suppliers.CompanyName" dt="string"/>
            <P3 dd="Products.ProductID" dt="i4"/>
            <P4 dd="Categories.CategoryName" dt="string"/>
            <P5 dd="Categories.CategoryID" dt="i4"/>
        </parameters>
    </constructor>
</implementation>

The SOAP request is as follows:

<SOAP:Body>
<complexMethod xmlns="http://schemas.cordys.com/1.0/dfilters">
       <P1 dd="Products.ProductName" dt="string">%h%</P1>
       <P2 dd="Suppliers.CompanyName" dt="string">
           <value>Exotic Liquids</value>
           <value>Tokyo Traders</value>
           <value>New Orleans Cajun Delights</value>
           <value>Leka Trading</value>
       </P2>
       <P3 dd="Products.ProductID" dt="i4">100</P3>
       <P4 dd="Categories.CategoryName" dt="string">%e%</P4>
       <P5 dd="Categories.CategoryID" dt="i4">1</P5>
    </complexMethod>
<SOAP:Body>

pp108 : Dynamic Queries

Dynamic Queries

Dynamic Filter

Dynamic Sort

List of Supported Operations